package com.zl.learning.db.dbutil;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.*;
import org.junit.Test;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

/**
 * Created by zhaolei on 2017/6/30.
 *
 * ArrayHandler：把结果集中的第一行数据转成对象数组。
 * ArrayListHandler：把结果集中的每一行数据都转成一个对象数组，再存放到List中。
 * BeanHandler：将结果集中的第一行数据封装到一个对应的JavaBean实例中。
 * BeanListHandler：将结果集中的每一行数据都封装到一个对应的JavaBean实例中，存放到List里。
 * KeyedHandler：将结果集中的每一行数据都封装到一个Map里，然后再根据指定的key把每个Map再存放到一个Map里。
 * MapHandler：将结果集中的第一行数据封装到一个Map里，key是列名，value就是对应的值。
 * MapListHandler：将结果集中的每一行数据都封装到一个Map里，然后再存放到List。
 * ScalarHandler：将结果集中某一条记录的其中某一列的数据存成Object。
 * ColumnListHandler：将结果集中某一列的数据存放到List中。
 *
 */
public class TestDbUtils {
    public static QueryRunner qRunner = new QueryRunner(ConnTools.getDataSource());
    UserField userField = new UserField();

    @Test
    public void testMapHandler() throws SQLException {
        // 以下部分代码采用MapHandler存储方式查询
        System.out.println("***Using MapHandler***");
        Map map = (Map) qRunner.query("select * from mc_user_field where id = ?", new MapHandler(), "1");

        System.out.println("id ------------- name ");
        System.out.println(map.get("id") + " ------------- " + map.get("name"));
    }

    @Test
    public void testMapListHandler() throws SQLException {
        // 以下部分代码采用MapListHandler存储方式查询
        System.out.println("***Using MapListHandler***");
        List lMap = qRunner.query( "select * from mc_user_field", new MapListHandler());

        System.out.println("id ------------- name ");
        for (Object aLMap : lMap) {
            Map vals = (Map) aLMap;
            System.out.println(vals.get("id") + " ------------- " + vals.get("name"));
        }
    }

    @Test
    public void testBeanHandler() throws ClassNotFoundException, SQLException {
        // 以下部分代码采用BeanHandler存储方式查询
        System.out.println("***Using BeanHandler***");
        userField = qRunner.query("select * from mc_user_field where id = ?", new BeanHandler<UserField>(UserField.class), "1");
        System.out.println("id ------------- name ");
        System.out.println(userField.getId() + " ------------- " + userField.getName());
    }

    @Test
    public void testBeanListHandler() throws SQLException {
        // 以下部分代码采用BeanListHandler存储方式查询
        System.out.println("***Using BeanListHandler***");
        List<UserField> lBean = qRunner.query("select * from mc_user_field", new BeanListHandler<UserField>(UserField.class));
        System.out.println("id ------------- name ");
        for (Object aLBean : lBean) {
            userField = (UserField) aLBean;
            System.out.println(userField.getId() + " ------------- " + userField.getName());
        }
    }

    @Test
    public void testArrayHandler() throws SQLException {
        // 以下部分代码采用ArrayHandler存储方式查询
        System.out.println("***Using ArrayHandler***");
        Object[] array = qRunner.query( "select * from mc_user_field where id = ?", new ArrayHandler(), "1");

        System.out.println("id ------------- name ");
        System.out.println(array[0].toString() + " ------------- " + array[1].toString());
    }

    @Test
    public void testArrayListHandler() throws SQLException {
        // 以下部分代码采用ArrayListHandler存储方式查询
        System.out.println("***Using ArrayListHandler***");
        List lArray = qRunner.query( "select * from mc_user_field", new ArrayListHandler());
        System.out.println("id ------------- name ");
        for (Object aLArray : lArray) {
            Object[] var = (Object[]) aLArray;
            System.out.println(var[0].toString() + " ------------- " + var[1].toString());
        }
    }

    @Test
    public void testScalarHandler() throws SQLException {
        // 以下部分代码采用ScalarHandler存储方式查询
        System.out.println("***Using ScalarHandler***");
        String name = qRunner.query("select * from mc_user_field where id = ?", new ScalarHandler<String>("name"), "1");

        System.out.println("name ");
        System.out.println(name);
    }

    @Test
    public void testColumnListHandler() throws SQLException {
        // 以下部分代码采用ColumnListHandler存储方式查询指定列
        System.out.println("***Using ColumnListHandler***");
        List lName = qRunner.query( "select * from mc_user_field where id = ?", new ColumnListHandler<String>("name"), "1");
        System.out.println("name ");
        for (Object aLName : lName) {
            String name = (String) aLName;
            System.out.println(name);
        }
    }

    @Test
    public void testKeyedHandler() throws SQLException {
        // 以下部分代码采用KeyedHandler存储方式查询
        System.out.println("***Using KeyedHandler***");
        Map<String, Map<String, Object>> map2 = qRunner.query( "select * from mc_user_field", new KeyedHandler<String>("name"));
        System.out.println("name: lisi");
        Map vals = map2.get("lisi");
        System.out.println(vals.get("id") + "\t" + vals.get("name") + "\t" + vals.get("type"));
    }

    @Test
    public void testInsert() {
        // 以下部分代码插入一条数据
        Connection conn = ConnTools.getConnection();
        System.out.println("***Insert begin***");
        userField = new UserField("lisi","学生","true","true","name");
        try {
            conn.setAutoCommit(false);
            Long id = qRunner.insert( "insert into mc_user_field (name,type,sort_order,required,visible)values (?,?,?,?,?)",new ScalarHandler<Long>("GENERATED_KEY"),
                    userField.getName(),
                    userField.getType(),
                    userField.getSort_order(),
                    userField.getRequired(),
                    userField.getVisible());
            DbUtils.commitAndClose(conn);
            System.out.println("insert data id = "+ id);
        } catch (SQLException e) {
            e.printStackTrace();
            DbUtils.rollbackAndCloseQuietly(conn);
        }
        System.out.println("***update end***");
    }

    @Test
    public void testUpdate()  {
        // 以下部分代码更新一条数据
        Connection conn = ConnTools.getConnection();
        System.out.println("***update begin***");
        userField = new UserField("test1","老师","true","true","老师");
        userField.setId(2);
        try {
            conn.setAutoCommit(false);
            qRunner.update( conn,"update mc_user_field set name = ?,type = ?,sort_order = ?,required = ?,visible = ?" + "where id = ?",
                    userField.getName(),
                    userField.getType(),
                    userField.getSort_order(),
                    userField.getRequired(),
                    userField.getVisible(),
                    userField.getId());
            DbUtils.commitAndClose(conn);
        } catch (SQLException e) {
            e.printStackTrace();
            DbUtils.rollbackAndCloseQuietly(conn);
        }
        System.out.println("***update end***");
    }

    @Test
    public void testDelete() {
        // 以下部分代码删除一条数据
        Connection conn = ConnTools.getConnection();
        System.out.println("***delete begin***");
        userField = new UserField();
        userField.setId(2);
        try {
            conn.setAutoCommit(false);
            qRunner.update(conn, "delete from mc_user_field where id = ?", new Object[]{userField.getId()});
            DbUtils.commitAndCloseQuietly(conn);
        } catch (SQLException e) {
            e.printStackTrace();
            DbUtils.rollbackAndCloseQuietly(conn);
        }
        System.out.println("***delete end***");
    }

}
